delete duplicates in excel. how to find duplicate values in excel using formula.__/LINKS\_ ► Facebook:➜ https://www.facebook.com/shahabislam123► Twitter:➜htt...

Intro by Chandeep Today we have a guest in the house. Please welcome, Puneet Gogia from ExcelChamps and he is going to teach us, 5 methods to remove duplicates in Excel.   Enters Puneet – While working with large data sets, duplicate values are always a problem No matter what, you will always get some duplicate values in your data. But the good news is, in Excel, you can find them, highlight them, and delete them in no time. Today, I will show you 5 effortless methods to figure out duplicates value in Excel. So let’s get started.   Instant Access: Download this Quick PDF guide to learn about these 5 methods.   Table of Contents Remove Duplicate Option Using COUNTIF Function Highlight With Conditional Formatting With a Pivot Table Using Power Query   Here I have a billing data with some duplicate invoices in it. You can download it from here to follow along.   1. Remove Duplicates Option It’s a default option in excel to remove duplicate values. With this, you can remove duplicate values in a flash. Here are the steps. Select the column from where you want to remove duplicate values (Here I’m selecting invoice column). After that, go to Data tab -> Data tools -> Remove Duplicates. Once you click on “Remove Duplicate”, you will get a pop-up option. From this option, you can select: Expand the Selection: Continue with the current selection: Select “Expand this Selection”, click “Remove Duplicates”. Congratulations, now there are no duplicate values in your data.   Different Between “Expand the Selection” & “Continue with Current Selection” Expand the Selection: This will consider entire row while checking for duplicate values. If there is a duplicate value in a cell but rest of the cells have unique values then that will not consider as a duplicate entry. Continue with Current Selection: It will just remove duplicates from selected column without considering values from other columns. But in real, this is not a good idea to use this option.   Pros: This method is super quick. You just need a few clicks. It will automatically delete duplicate values.   Cons: You can’t review duplicate values from your data Data should be sort before you remove duplicates   2. Using COUNTIF Function An advance method to remove duplicate values in excel. And, my favorite one. Just follow these simple steps. Enter the above formula in your data table. Now, drag this formula up to the last row of the data. Filter the column and select values which are more than 1. All the values which you have selected are in duplicate. You can review them and then delete them.   Pros: It’s a one time set up, you don’t have to apply it again and again. It’s a dynamic method, it will get update with data.   Cons: Applying formulas may be confusing sometimes. You can check duplicate values from a single column at a time. You have to extend the formula to check other columns.   3. Highlight With Conditional Formatting You can highlight duplicate values with a color. Yes, conditional formatting can help you to highlight duplicate values and after that, you can review them, and delete them. Steps are below. Select the entire your data from which you want to highlight duplicate values. Go to Home Tab -> Styles -> Conditional Formatting -> Highlight Cells Rules -> Duplicate Values. Here, you’ll get a pop-up option to select a color to highlight values. Select the color, you want to use. Click OK.   This will highlight all the duplicate values from the data you have selected. Now, you can review them and delete them. You can also highlight unique values instead of duplicate values.   Pros: It’s quick and easy. You can review duplicate values before deleting them.   Cons: This is not dynamic, you have to update conditional formatting when you update your data. You have to delete duplicate values manually.   4. Using a Pivot Table Maybe it looks silly to you but you can also use a pivot table to figure out duplicate values from your data. Let’s me tell you how you can do this. Here I am using the same invoice data which I have used in above methods. Follow these steps. First of all, you have to insert a pivot table. And after that, using pivot table fields dialog box, add invoices in “Rows” field. Again, select invoices and add it into “Values” field. Now, change value setting to “count”. In the end, you have a pivot table like below. In this pivot, all the invoices where the count is more than 1 are in duplicate in your data. Now, you can review duplicate values from your raw data and delete them accordingly. Pros: One time set up. A pivot table is dynamic if you are using auto update.   Cons: You can’t directly delete duplicate values. You have to delete values manually.   5. Using Power Query Power Query is a revolution for data cleaning. You can easily use it to remove duplicate values from your data. Make sure to apply table to your raw data. And, follow these steps to remove duplicates with power query. Select any of the cells from your data. Go to -> Data Tab -> Get & Transform -> From table. Once you click on the button, it will load your data into power query editor. Now, right-click on the column header from which you want to check duplicate values. Select “Remove Duplicate” (It will instantly remove all the duplicate values from your data.) And after that, click on “Close & Load” (It will paste the entire data into a new worksheet without duplicate values). Pros: It’s fully dynamic. Whenever you update your data, you just have to refresh your query and it will automatically remove all the duplicates. It will not make any changes in your main data, in fact, you’ll get new transformed data which will be connected with your original data.   Cons: It’s a long process. Not possible if you don’t have power query in your excel version. [...]

how to vlookup and return multiple corresponding values vertically in excel.__/LINKS\_ ► Facebook:➜ https://www.facebook.com/shahabislam123► Twitter:➜https:/...

In this quick tip Excel tutorial we are learning how to remove or delete the formulas in workbook but keep the values or data.This approach does not require ...

In this quick tip Excel tutorial we are learning how to remove or delete the formulas in workbook but keep the values or data.This approach does not require ...

To highlight duplicate or unique values in Excel: • Select the cell range • Under home select conditional formatting • Select highlight cell rules • Select duplicate values • From here you can set it to highlight duplicate or unique and change the color that you want these values to be highlighted

In this Excel short tutorial we are learning how to change or correct the text case to sentence case, lower case (small letters) or upper case (capital lette...

Learn how to calculate moving or rolling averages in Excel based on variable average interval using a simple to understand Excel formula. Formula approach up...

Excel SUM formula includes all values in a range. Using he SUBTOTAL function is the easiest way to overcome limitations and filtered values.

In this quick tip Excel tutorial we are learning how to remove or delete the formulas in workbook but keep the values or data.This approach does not require ...

In this quick tip Excel tutorial we are learning how to remove or delete the formulas in workbook but keep the values or data.This approach does not require ...

Remove values in Excel with ease using Go To Special feature. Learn how to select only values and delete them, or use a macro to automate the process.

Countif formula is used to find the repetition of value. See on video. Countif syntax: =countif(Range,value) Example: =Countif(b2:b17,"Anna") Explanation: - Countif: -Excel formula. b2:b17: Range (Cell Address) "Anna": Value (Formula search "Anna" value in given range)

How to select highest three or lowest three value in excel 2010 using formula?In this video we are about to learn how to find top 10/5/3 values or lowest 10/...

Understand how to use SUM as a subtraction formula in excel here! By gaining the understanding, you will have an alternative method to subtract numbers in excel

WEEKNUM Formula in Excel - Learn how to get the week number from a date in Excel in just a few steps! I will show you in this free tutorial...

OFFSET formula in Excel is used to return a range that is a specified number of rows and columns from a reference cell or range...

MATCH formula looks for an item within a range and returns the relative position of that item in the range. It is used in combination of the INDEX function.

CHOOSE Formula in Excel is used to index a number and return a desired comment based on the number. The formula is explained properly in the post...